erDiagram
SKILLS ||--o{ PEOPLESKILLS : includes
PEOPLE ||--o{ PEOPLESKILLS : acquires
PEOPLE ||--o{ PEOPLE_ROLES : has
ROLES ||--o{ PEOPLE_ROLES : assigned
SKILLS {
int id
string name
string description
string tag
string url
string time_commitment
}
PEOPLESKILLS {
int id
int skills_id
int people_id
date date_acquired
}
PEOPLE {
int id
string first_name
string last_name
string email
string linkedin_url
string headshot_url
string discord_handle
string brief_bio
date date_joined
}
PEOPLE_ROLES {
int id
int people_id
int role_id
date date_role_acquired
}
ROLES {
int id
string name
int sort_priority
}
Homework 6 - Creating a resume database
The document includes a Python-based script that interacts with a MySQL database to run queries, execute DDL files, and display database contents in HTML format. Using SQLAlchemy for database connections, pandas for data manipulation, and .env file configuration for secure credential handling, it provides functions to connect to a database, execute SQL queries, handle various SQL and connection errors, and return results as HTML tables. Additionally, the project structure includes .env file configurations, automated DDL execution, and SQL query examples for common data retrieval tasks.
GITHUB URL: https://github.com/cmsc-vcu/cmsc408-fa2024-hw6-alexalauer
Overview and description
The project involves creating a database to capture and organize the collective skills, certifications, and roles of a small team of designers and developers in a company run by an eccentric “sensei.” This “company resume” database will store information about each team member’s skills and roles, allowing the sensei to quickly find employees with specific expertise, view individual skill profiles, and identify skill gaps.
The database will have tables for skills, employees, roles, and the relationships between them. The Skills table will store each skill’s name, description, a tag, URL, and time commitment, including skills the team aims to acquire in the future. The Employees table will capture each employee’s personal and professional information, including LinkedIn and Discord details, headshots, and bios. The Roles table defines each role’s name and display priority, supporting well-organized reports.
Two relationship tables will connect employees with skills and roles. The Employee Skills table will log when employees acquire each skill, including anticipated completion dates for in-progress certifications. The Employee Roles table will track role assignments, storing the start date for each role an employee holds. This setup will give the sensei easy access to the company’s skillset, supporting better planning and growth strategies.
Crows-foot diagram
The Crow’s Foot diagram illustrates the relationships and attributes among five key entities in a database schema designed to manage information about people, their skills, roles, and the associations between them.
Entities:
PEOPLE: This entity contains details about individuals, including their first name, last name, email address, LinkedIn profile, headshot, Discord handle, brief biography, and the date they joined.
SKILLS: This entity lists various skills available, with attributes such as the skill’s name, description, associated tag, URL for more information, and the time commitment required to acquire or develop the skill.
PEOPLESKILLS: This associative entity captures the relationship between people and their skills, indicating which skills a person has acquired and the date of acquisition.
ROLES: This entity defines different roles within the organization, with attributes that include the role name and its sort priority, determining the order of importance or hierarchy.
PEOPLE_ROLES: This associative entity links people to the roles they hold within the organization, including the date each role was acquired.
Relationships:
Acquisition of Skills: Each person can acquire multiple skills, creating a one-to-many relationship from PEOPLE to PEOPLESKILLS. This indicates that one person can have many skills, while each skill entry corresponds to one specific individual.
Role Assignments: Each person can hold multiple roles, represented by the relationship between PEOPLE and PEOPLE_ROLES. This reflects the idea that a person can assume different positions within an organization over time.
Skill Listings: The SKILLS entity is linked to PEOPLESKILLS, showing that a skill can be held by multiple people.
Role Definitions: The ROLES entity is linked to PEOPLE_ROLES, indicating that a role can be assigned to multiple individuals, showcasing the flexibility of role distribution within the organization.
Overall, this diagram provides a clear visual representation of how individuals are related to their skills and roles, facilitating better understanding and management of personnel and their capabilities within an organization.
Loading the database
No errors detected while loading: ./my-ddl.sql
Examples of data in the database
The following sections provide an overview of the schema including table names, and number of rows and columns in each table.
For the people, skills, and roles tables, a description of each table is presented along with it’s contents.
Tables and metrics in the database
| TableName | RecordCount | ColumnCount |
|---|---|---|
| people | 10 | 9 |
| peopleroles | 15 | 4 |
| peopleskills | 26 | 4 |
| roles | 0 | 3 |
| skills | 2 | 6 |
People table
The people table contains elements that describe the information about an employee
Below is a list of data in the people table.
sql = f"""
select * from people;
"""
run_sql_and_return_df( sql,cnx )| id | first_name | last_name | linkedin_url | headshot_url | discord_handle | brief_bio | date_joined | |
|---|---|---|---|---|---|---|---|---|
| 201 | Alex | Person 1 | alex.person1@example.com | https://linkedin.com/in/alexperson1 | None | alex#1234 | A motivated designer. | 2023-01-15 |
| 202 | Jordan | Person 2 | jordan.person2@example.com | https://linkedin.com/in/jordanperson2 | None | jordan#2345 | Skilled in front-end development. | 2022-11-20 |
| 203 | Taylor | Person 3 | taylor.person3@example.com | https://linkedin.com/in/taylorperson3 | None | taylor#3456 | Passionate about UX/UI. | 2021-08-05 |
| 204 | Casey | Person 4 | casey.person4@example.com | https://linkedin.com/in/caseyperson4 | None | casey#4567 | Backend developer extraordinaire. | 2023-03-12 |
| 205 | Morgan | Person 5 | morgan.person5@example.com | https://linkedin.com/in/morganperson5 | None | morgan#5678 | Data scientist in training. | 2022-07-22 |
| 206 | Reese | Person 6 | reese.person6@example.com | https://linkedin.com/in/reeseperson6 | None | reese#6789 | Loves to work with APIs. | 2021-06-11 |
| 207 | Blake | Person 7 | blake.person7@example.com | https://linkedin.com/in/blakeperson7 | None | blake#7890 | Machine learning enthusiast. | 2023-02-08 |
| 208 | Alexis | Person 8 | alexis.person8@example.com | https://linkedin.com/in/alexperson8 | None | alexis#8901 | Front-end designer. | 2020-12-14 |
| 209 | Drew | Person 9 | drew.person9@example.com | https://linkedin.com/in/drewperson9 | None | drew#9012 | QA engineer with a keen eye. | 2022-05-03 |
| 210 | Robin | Person 10 | robin.person10@example.com | https://linkedin.com/in/robinperson10 | None | robin#0123 | Specializes in cloud services. | 2023-04-19 |
Skills table
The skills table contains elements that describe the possible skills the employees have
Below is a list of data in the skills table.
sql = """
select * from skills
"""
run_sql_and_return_df( sql, cnx )| id | name | description | tag | url | time_commitment |
|---|---|---|---|---|---|
| 101 | Animal Tracking | The ability to track animal locations via footprints | Skill 1 | None | Medium |
| 102 | Sock Matching | Expert ability to find matching pairs of socks in a laundry pile | Skill 2 | None | Low |
| 103 | Pencil Spinning | Advanced skill in twirling a pencil around fingers without dropping it | Skill 3 | None | Low |
| 104 | Impressive Yawning | Mastery in performing dramatic yawns that influence others to yawn | Skill 4 | None | Minimal |
| 105 | One-Handed Clapping | Ability to create sound by clapping with just one hand | Skill 5 | None | Medium |
| 106 | Spontaneous Dancing | Skill in breaking into dance at random moments | Skill 6 | None | Low |
| 107 | Refrigerator Tetris | Talent in arranging food items in the fridge to maximize space | Skill 7 | None | Medium |
| 108 | Pet Whispering | The ability to hold engaging conversations with pets and understand their “replies” | Skill 8 | None | High |
Roles table
The roles table contains elements that describe all of the roles in the company an employee can hold
Below is a list of data in the roles table.
sql = """
select * from roles
"""
run_sql_and_return_df( sql, cnx )| id | name | sort_priority |
|---|---|---|
| 401 | Designer | 10 |
| 402 | Developer | 20 |
| 403 | Recruit | 30 |
| 404 | Team Lead | 40 |
| 405 | Boss | 50 |
| 406 | Mentor | 60 |
Sample queries
List people names and email addresses ordered by last_name
sql = """
SELECT CONCAT(people.first_name, ' ', people.last_name) AS full_name, people.email
FROM people
ORDER BY people.last_name ASC;
"""
run_sql_and_return_df( sql, cnx )| full_name | |
|---|---|
| Alex Person 1 | alex.person1@example.com |
| Robin Person 10 | robin.person10@example.com |
| Jordan Person 2 | jordan.person2@example.com |
| Taylor Person 3 | taylor.person3@example.com |
| Casey Person 4 | casey.person4@example.com |
| Morgan Person 5 | morgan.person5@example.com |
| Reese Person 6 | reese.person6@example.com |
| Blake Person 7 | blake.person7@example.com |
| Alexis Person 8 | alexis.person8@example.com |
| Drew Person 9 | drew.person9@example.com |
List skill names of Person 1
sql = """
SELECT
CONCAT(people.first_name, ' ', people.last_name) AS "Employee name",
skills.name AS skill_name
FROM
peopleskills
JOIN skills ON peopleskills.skills_id = skills.id
JOIN people ON peopleskills.people_id = people.id
WHERE
people.last_name = 'Person 1'
"""
run_sql_and_return_df( sql, cnx )| Employee name | skill_name |
|---|---|
| Alex Person 1 | Animal Tracking |
| Alex Person 1 | Pencil Spinning |
| Alex Person 1 | Spontaneous Dancing |
List people names with Skill 6
sql = """
SELECT
CONCAT(people.first_name, ' ', people.last_name) AS "Employee name"
FROM
peopleskills
JOIN skills ON peopleskills.skills_id = skills.id
JOIN people ON peopleskills.people_id = people.id
WHERE
skills.tag = 'Skill 6'
"""
run_sql_and_return_df( sql, cnx )| Employee name |
|---|
| Alex Person 1 |
| Morgan Person 5 |
| Blake Person 7 |
| Alexis Person 8 |
| Drew Person 9 |
List people with a DEVELOPER role
sql = """
SELECT
CONCAT(people.first_name, ' ', people.last_name) AS "Employee name"
FROM
peopleroles
JOIN roles ON peopleroles.roles_id = roles.id
JOIN people ON peopleroles.people_id = people.id
WHERE
roles.name = 'Developer'
"""
run_sql_and_return_df( sql, cnx )| Employee name |
|---|
| Alex Person 1 |
| Taylor Person 3 |
| Reese Person 6 |
| Drew Person 9 |
| Robin Person 10 |
List names and email addresses of people without skills
sql = """
SELECT
CONCAT(people.first_name, ' ', people.last_name) AS "Employee name", people.email
FROM
people
LEFT JOIN peopleskills ON people.id = peopleskills.people_id
WHERE
peopleskills.people_id IS NULL
"""
run_sql_and_return_df( sql, cnx )| Employee name | |
|---|---|
| Casey Person 4 | casey.person4@example.com |
List people names and skill names with the BOSS role
sql = """
SELECT
CONCAT(people.first_name, ' ', people.last_name) AS "Employee Name",
skills.name AS "Skill Name"
FROM
people
JOIN peopleroles ON people.id = peopleroles.people_id
JOIN roles ON peopleroles.roles_id = roles.id
JOIN peopleskills ON people.id = peopleskills.people_id
JOIN skills ON peopleskills.skills_id = skills.id
WHERE
roles.name = 'Boss';
"""
run_sql_and_return_df( sql, cnx )| Employee Name | Skill Name |
|---|---|
| Jordan Person 2 | Pencil Spinning |
| Jordan Person 2 | Impressive Yawning |
| Jordan Person 2 | One-Handed Clapping |
List ids and names of unused roles
sql = """
SELECT
roles.id, roles.name
FROM
roles
LEFT JOIN peopleroles ON roles.id = peopleroles.roles_id
WHERE peopleroles.roles_id IS NULL
"""
run_sql_and_return_df( sql, cnx )| id | name |
|---|---|
| no records returned |
Reflection
I struggled a little with getting all the tables to work properly, there are a lot of small syntax nuances that will make the program crash if not done properly. The erorr messages were incredibly helpful to find some of these, but often I needed to search through any of SQL code to find where I was missing a commma or something of the sort. As it is the first table in my report still does not calculate everything correctly. For some reason it stil says that my roles table has a recordCount of 0, even though it does not. Overall, I think the assignment helped me understand how to make tables in a ddl from scratch very effectively.
For homework 7, I had difficultly at the beginning of the assingnment due to my naming conventions from homework 6 and therefore had to go back to my ddl and rename all of my columns (for the autograder). Once all of the tables were sorted out, I jumped right into the queries. I had already completed the first two the week before and needed to do the remaining ones. Starting off I was able to piece together the pieces until I hit the Developer query. I know other students had the same issue where the peopleroles table was being made but there was nothing inside of it even though the INSERT commands were present. It took hours to determine the issue was that I needed a COMMIT; after the code. From then on, I was once again able to piece everything togther. Some of the queries were difficult to get a hang of, but eventually the syntax was starting to make sense.